"""
Case Type   : 临时视图
Case Name   : 修改在普通列存表中创建的临时视图，部分测试点合理报错
Create At   : 2024/10/17
Owner       : zhanghuan96
Description :
    1.创建用户，创建普通行存表，并插入数据
    2.创建临时视图指定用户
    3.查询临时视图schema
    4.创建用户
    5.修改临时视图的用户
    6.修改临时视图已有列名
    7.建表
    8.修改临时视图的精度
    9.建表
    10.修改临时视图的列类型
    11.建表
    12.更改普通视图的数据源
    13.f'{self.u_name_01}'用户给表插入数据
    14.f'{self.u_name}'用户给表插入数据
    15.查询
    16.删除普通视图的已有的列
    17.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.报错
    7.成功
    8.报错
    9.成功
    10.报错
    11.成功
    12.成功
    13.报错，权限拒绝
    14.成功
    15.成功
    16.报错
    17.成功
History     :
"""

import os
import unittest

import py_opengauss
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class ListPartitionCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.user_node = Node('PrimaryDbBmode')
        self.primary_sh = CommonSH('PrimaryDbBmode')
        self.constant = Constant()
        self.u_name = 'u_grammar_0022'
        self.u_name_01 = 'u_grammar_0022_01'
        self.table_name = 't_grammar_0022'
        self.v_name = 'v_grammar_0022'
        self.con_info = f'opengauss://' \
                        f'{self.user_node.db_user}:' \
                        f'{self.user_node.db_password}@' \
                        f'{self.user_node.db_host}:' \
                        f'{self.user_node.db_port}/' \
                        f'{self.user_node.db_name}'
        self.con_info1 = f'opengauss://' \
                         f'{self.u_name_01}:' \
                         f'{macro.COMMON_PASSWD}@' \
                         f'{self.user_node.db_host}:' \
                         f'{self.user_node.db_port}/' \
                         f'{self.user_node.db_name}'

    def test_partition_ddl_dml(self):
        step = '--step1:创建用户，创建列存表，并插入数据 expect:成功--'
        self.log.info(step)
        sql_cmd = f'''drop user if exists {self.u_name};
        create user {self.u_name} password '{macro.COMMON_PASSWD}';
        grant all privileges to {self.u_name};
        drop table if exists {self.table_name};
        create table {self.table_name}(c1 int,c2 text,c3 varchar(10))\
        with(orientation=column);
        insert into {self.table_name} values(1,1,1),(2,'a','b');'''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS,
                      result, "执行失败:" + step)
        self.assertIn('INSERT 0 2', result)

        step = '--step2:创建临时视图指定用户expect:成功--'
        self.log.info(step)
        self.db1 = py_opengauss.open(self.con_info)
        create_result = self.db1.query(f'''
        create or replace definer={self.u_name} temp view {self.v_name}(a,b,c)\
         with(security_barrier=true)as select c1,c2,c3 from \
        {self.table_name};''')
        self.log.info(create_result)
        self.assertIn('CREATE VIEW', create_result)

        step = '--step3:查询临时视图schema expect:成功--'
        self.log.info(step)
        sch_result = self.db1.query(f'''select nspname from pg_namespace \
        where oid = (select relnamespace from pg_class where \
        relname = '{self.v_name}');''')
        self.log.info(sch_result)
        query_res = sch_result[0][0]
        self.log.info(query_res)

        step = '--step4:创建用户expect:成功--'
        self.log.info(step)
        ddl_sql = f'''drop user if exists {self.u_name_01};'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        ddl_sql = f'''create user {self.u_name_01} password \
        '{macro.COMMON_PASSWD}';'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn('CREATE ROLE', result)

        step = '--step5:修改临时视图的用户expect:成功--'
        self.log.info(step)
        ddl_sql = f'''alter definer={self.u_name_01} view \
        {query_res}.{self.v_name}(a,b,c) as select c1,c2,c3 from \
        {self.table_name};'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn('ALTER VIEW', result)

        step = '--step6:修改临时视图已有列名expect:报错--'
        self.log.info(step)
        ddl_sql = f'''alter definer={self.u_name} view \
       {query_res}.{self.v_name}(a,b,d) as select  c1,c2,c3 from \
        {self.table_name};'''
        try:
            self.db1.query(ddl_sql)
        except Exception as e:
            self.log.info(e)
            self.assertIn('cannot change name of view column', str(e))

        step = '--step7:建表expect:成功--'
        self.log.info(step)
        ddl_sql = f'''drop table if exists {self.table_name}_01;'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        ddl_sql = f'''create table {self.table_name}_01\
        (c1 int,c2 text,c3 varchar(30));'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn('CREATE TABLE', result)

        step = '--step8:修改临时视图的精度expect:报错--'
        self.log.info(step)
        ddl_sql = f'''alter definer={self.u_name} view \
              {query_res}.{self.v_name}(a,b,c) as select  c1,c2,c3 from \
              {self.table_name}_01;'''
        try:
            self.db1.query(ddl_sql)
        except Exception as e:
            self.log.info(e)
            self.assertIn('cannot change data type of view column '
                          '"c" from character varying(10) to character '
                          'varying(30)', str(e))

        step = '--step9:建表expect:成功--'
        self.log.info(step)
        ddl_sql = f'''drop table if exists {self.table_name}_02;'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        ddl_sql = f'''create table {self.table_name}_02\
        (c1 int,c2 int,c3 text);'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn('CREATE TABLE', result)

        step = '--step10:修改临时视图的列类型expect:报错--'
        self.log.info(step)
        ddl_sql = f'''alter definer={self.u_name} view \
                  {query_res}.{self.v_name}(a,b,c) as select  c1,c2,c3 from \
                  {self.table_name}_02;'''
        try:
            self.db1.query(ddl_sql)
        except Exception as e:
            self.log.info(e)
            self.assertIn('cannot change data type of view column "b" '
                          'from text to integer', str(e))

        step = '--step11:建表expect:成功--'
        self.log.info(step)
        ddl_sql = f'''drop table if exists {self.table_name}_03;'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        ddl_sql = f'''create table {self.table_name}_03\
        (c1 int,c2 text,c3 varchar(10));'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn('CREATE TABLE', result)

        step = '--step12:更改普通视图的数据源expect:成功--'
        self.log.info(step)
        ddl_sql = f'''alter definer={self.u_name} view \
          {query_res}.{self.v_name}(a,b,c) as select  c1,c2,c3 from \
          {self.table_name}_03;'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn('ALTER VIEW', result)

        step = "--step13:f'{self.u_name_01}'用户给表插入数据expect:报错，权限拒绝--"
        self.log.info(step)
        self.db2 = py_opengauss.open(self.con_info1)
        ddl_sql = f'''insert into {self.table_name}_03 \
        values(1,1,1),(2,'a','b');'''
        try:
            self.db2.query(ddl_sql)
        except Exception as e:
            self.log.info(e)
            self.assertIn('permission denied for relation '
                          't_grammar_0022_03', str(e))

        step = "--step14:f'{self.u_name}'用户给表插入数据expect:成功--"
        self.log.info(step)
        ddl_sql = f'''insert into {self.table_name}_03 \
                values(1,1,1),(2,'a','b');'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn('INSERT', result)

        step = '--step15:查询expect:成功--'
        self.log.info(step)
        ddl_sql = f'''select * from {query_res}.{self.v_name};'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        expect = [(1, '1', '1'), (2, 'a', 'b')]
        self.assertEqual(result, expect)

        step = '--step16:删除普通视图的已有的列expect:报错--'
        self.log.info(step)
        ddl_sql = f'''alter definer={self.u_name_01} view \
                  {query_res}.{self.v_name}(a,b) as select  c1,c2 from \
                  {self.table_name}_03;'''
        try:
            self.db1.query(ddl_sql)
        except Exception as e:
            self.log.info(e)
            self.assertIn('cannot drop columns from vie', str(e))

    def tearDown(self):
        step = '--step17:恢复环境,关闭session,删除测试表及用户--'
        self.log.info(step)
        self.db1.close()
        self.db2.close()
        result1 = self.primary_sh.execut_db_sql(f'drop view if exists '
                                                f'{self.v_name};')
        self.log.info(result1)
        result2 = self.primary_sh.execut_db_sql(f'drop user {self.u_name};'
                                                f'drop user {self.u_name_01};')
        self.log.info(result2)
        result3 = self.primary_sh.execut_db_sql(f'drop table if exists '
                                                f'{self.table_name},'
                                                f'{self.table_name}_01,'
                                                f'{self.table_name}_02,'
                                                f'{self.table_name}_03;')
        self.log.info(result3)
        self.assertIn('DROP ROLE', result2, "执行失败:" + step)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      result3, "执行失败:" + step)
        self.log.info(f'-----{os.path.basename(__file__)} end--')
